﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
//------------//
using QLKTX1._0.DTO;
using QLKTX1._0.DB;
using System.Data.SqlClient;
using System.Data;
//....................
namespace QLKTX1._0.DAO
{
    class ChiTietKhungGiaDienNuocDAO
    {
        public static List<ChiTietKhungGiaDienNuocDTO> dsChiTietKhungGiaDienNuoc()
        {
            List<ChiTietKhungGiaDienNuocDTO> dsChiTietKhungGiaDienNuocDot = new List<ChiTietKhungGiaDienNuocDTO>();

            ConnectDB con = new ConnectDB();
            string sql = "SELECT dbo.loaiChiPhi.maLoai_chiPhi, dbo.loaiChiPhi.tenLoai_chiPhi, dbo.loaiChiPhi.donViTinh, dbo.khungGia_dienNuoc.maKhungGia_dienNuoc,"; 
            sql+=" dbo.khungGia_dienNuoc.moTa, dbo.chiTiet_khungGia_dienNuoc.giaThanh";
            sql+=" FROM dbo.chiTiet_khungGia_dienNuoc INNER JOIN";
            sql+=" dbo.loaiChiPhi ON dbo.chiTiet_khungGia_dienNuoc.maLoai_chiPhi = dbo.loaiChiPhi.maLoai_chiPhi INNER JOIN";
            sql += " dbo.khungGia_dienNuoc ON dbo.chiTiet_khungGia_dienNuoc.maKhungGia_dienNuoc = dbo.khungGia_dienNuoc.maKhungGia_dienNuoc";
            DataTable dt = con.getDataTable(sql);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                ChiTietKhungGiaDienNuocDTO ctkgdn = new ChiTietKhungGiaDienNuocDTO();

                KhungGiaDienNuocDTO kgdn = new KhungGiaDienNuocDTO();
                kgdn.MaKhungGiaDienNuoc = dt.Rows[i]["maKhungGia_dienNuoc"].ToString();
                kgdn.MoTa = dt.Rows[i]["moTa"].ToString();

                LoaiChiPhiDTO lcp = new LoaiChiPhiDTO();
                lcp.MaLoaiChiPhi = dt.Rows[i]["maLoai_chiPhi"].ToString();
                lcp.TenLoaiChiPhi=dt.Rows[i]["tenLoai_chiPhi"].ToString();
                lcp.DonViTinh=dt.Rows[i]["donViTinh"].ToString();

                ctkgdn.MaKhungGiaDienNuoc = kgdn;
                ctkgdn.MaLoaiChiPhi  = lcp;

                ctkgdn.GiaThanh = Convert.ToInt32(Double.Parse(dt.Rows[i]["giaThanh"].ToString()));

                dsChiTietKhungGiaDienNuocDot.Add(ctkgdn);
            }

            return dsChiTietKhungGiaDienNuocDot;

        }
        //Ham Insert
        public static bool themChiTietKhungGiaDienNuoc(ChiTietKhungGiaDienNuocDTO k)
        {
           ConnectDB con = new ConnectDB();// dung DB

            return con.executeQuery("INSERT INTO chiTiet_khungGia_dienNuoc(maKhungGia_dienNuoc,maLoai_chiPhi,giaThanh) VALUES('" + k.MaKhungGiaDienNuoc .MaKhungGiaDienNuoc  + "','" + k.MaLoaiChiPhi .MaLoaiChiPhi  + "',"+k.GiaThanh +")");
        }
        //Ham Delete
        public static bool xoaChiTietKhungGiaDienNuoc(ChiTietKhungGiaDienNuocDTO k)
        {
            ConnectDB con = new ConnectDB();

            return con.executeQuery("DELETE FROM chiTiet_khungGia_dienNuoc WHERE maKhungGia_dienNuoc='" + k.MaKhungGiaDienNuoc.MaKhungGiaDienNuoc + "'and maLoai_chiPhi='" + k.MaLoaiChiPhi.MaLoaiChiPhi + "'");
        }
        //Ham Update
        public static bool capNhatChiTietKhungGiaDienNuoc(ChiTietKhungGiaDienNuocDTO k)
        {
            ConnectDB con = new ConnectDB();

            return con.executeQuery("UPDATE chiTiet_khungGia_dienNuoc set giaThanh='" + k.GiaThanh + "' WHERE maKhungGia_dienNuoc='" + k.MaKhungGiaDienNuoc.MaKhungGiaDienNuoc + "'and maLoai_chiPhi='" + k.MaLoaiChiPhi.MaLoaiChiPhi + "'");
        }
    }
}
